﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Inter.MianShi.Model;
using System.Data;

namespace Inter.MianShi.DAL
{
    public class CompanyDal
    {
        string str = "Data Source=.;Initial Catalog=MianShiHuiZong;Integrated Security=True";
        /// <summary>
        /// 添加公司
        /// </summary>
        /// <returns></returns>
        public int CompanyAdd(Company co)
        {
            SqlConnection con = new SqlConnection(str);
            con.Open();
            string sql =string.Format( "insert into Company values('{0}','{1}')",co.CompanyName,co.CompanyCompany);
            SqlCommand com = new SqlCommand(sql,con);
            int i = com.ExecuteNonQuery();
            con.Close();
            return i;
        }
        /// <summary>
        /// 显示公司信息
        /// </summary>
        /// <returns></returns>
        public List<Company> ShowCompany()
        {
            string sql = "select CompanyId,CompanyName,CompanyCompany from Company";
            SqlDataAdapter adapter = new SqlDataAdapter(sql,str);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            List<Company> list = new List<Company>();
            foreach (DataRow d in dt.Rows)
            {
                Company c = new Company();
                c.CompanyId = Convert.ToInt32(d["CompanyId"]);
                c.CompanyName = d["CompanyName"].ToString();
                c.CompanyCompany = d["CompanyCompany"].ToString();
                list.Add(c);
            }
            return list;
        }
        /// <summary>
        /// 根据编号删除公司信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelCompany(int id)
        {
            SqlConnection con = new SqlConnection(str);
            con.Open();
            string sql = "delete from Company where CompanyId="+id+"";
            SqlCommand com = new SqlCommand(sql,con);
            int i = com.ExecuteNonQuery();
            con.Close();
            return i;
        }
        /// <summary>
        /// 根据编号获取公司信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Company GetCompanyId(int id)
        {
            SqlConnection con = new SqlConnection(str);
            con.Open();
            string sql = "select * from Company where CompanyId="+id+"";
            SqlCommand com = new SqlCommand(sql,con);
            SqlDataReader reader = com.ExecuteReader();
            Company c = new Company();
            while(reader.Read())
            {
                c.CompanyId = Convert.ToInt32(reader["CompanyId"]);
                c.CompanyName = reader["CompanyName"].ToString();
                c.CompanyCompany = reader["CompanyCompany"].ToString();
            }
            con.Close();
            return c;
        }
        /// <summary>
        /// 修改公司信息
        /// </summary>
        /// <param name="c"></param>
        /// <returns></returns>
        public int Uptdate(Company c)
        {
            SqlConnection con = new SqlConnection(str);
            con.Open();
            string sql = string.Format("update Company set CompanyName='{0}' , CompanyCompany='{1}' where CompanyId={2}",c.CompanyName,c.CompanyCompany,c.CompanyId);
            SqlCommand com = new SqlCommand(sql,con);
            int i = com.ExecuteNonQuery();
            con.Close();
            return i;
        }
    }
}
